package com.iris.live.services.report.sources;

import com.google.common.collect.Maps;
import com.iris.live.services.common.DateFormatUtils;
import com.iris.live.services.data.models.ReportConfig;
import com.iris.live.services.report.data.BaseReportDataSource;
import com.iris.live.services.report.data.ReportContext;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by lichao on 2017/9/5.
 */
@Component("StockForecastDataSource")
public class StockForecastDataSource extends BaseReportDataSource {

    @Override
    public List getData(ReportContext context, List<ReportConfig> config) throws Exception {
        Map info=context.getParams();
        String lineType=info.get("displayType").toString();
        List<Map> datas =new ArrayList<>();
        if("1".equals(lineType)){
            datas=  getColorLineData(info);
        }
        else if("2".equals(lineType)){
            datas=  getModelLineData(info);
        }else if("3".equals(lineType)){
            datas = getModelAndColorLineData(info);
        }else if("4".equals(lineType)) {
            datas = getModelAndColorLineDateAll(info);
        }
        return  datas;
    }

    private List getColorLineData(Map info){
        String sql = "select a.颜色,a.报价数,IFNULL(b.库存,0)库存,IFNULL(未分配资源,0)未分配资源\n" +
                "from\n" +
                "(\n" +
                "select concat(a.外观,'/',a.内饰) 颜色, sum(报价数) 报价数 \n" +
                "from (\n" +
                "\t\t\t\tselect a.品牌, a.级别,a.车型,a.外观,a.内饰, 1 AS 报价数 from \n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\tselect a.初始报价单号,a.许可,b.车型, 来源, b.品牌, b.级别,a.外观,a.内饰\n" +
                "\t\t\t\t\t\t\t\tFROM 报价单表 a, LSH车型 b \n" +
                "\t\t\t\t\t\t\t\tWHERE a.车型编号 = b.车型编号\n" +
                "\t\t\t\t\t\t\t\tAND date_format(a.`报价时间`,'%Y-%m-%d') >= :startDate and date_format(a.`报价时间`,'%Y-%m-%d') <= :endDate  " +
                "                and a.许可 = :permit \n" +
                "\t\t\t\t\t\t\t\tORDER BY 报价编号 DESC\n" +
                "\t\t\t\t)a GROUP BY 初始报价单号 having 1=1\n" +
                ")a GROUP BY 外观,内饰\n" +
                ")a\n" +
                "LEFT JOIN\n" +
                "\n" +
                "(\n" +
                "SELECT  concat(c.外观,'/',c.内饰) 颜色,count(*) 库存\n" +
                "                \t      FROM   LSH车型 a, LSH库存表 b LEFT JOIN vw_stock_quotation c ON c.库存编号 = b.库存编号    \n" +
                "                \t      WHERE a.车型编号 = b.车型编号  \n" +
                "                         AND b.批发日期 <= :endDate and b.is_delete = 0 and b.车架号 not like 'IRIS%' \n" +
                "                         AND  (c.开票时间 IS NULL \n" +
                "                               OR (date_format(c.开票时间,'%Y-%m-%d') > :endDate and c.退票时间 IS NULL) \n" +
                "                               OR (date_format(c.开票时间,'%Y-%m-%d') > :endDate and c.退票时间 < c.开票时间) \n" +
                "                              ) \n" +
                "               and b.所属经销商 = :permit and 1=1" +
                "GROUP BY c.外观,c.内饰 \n" +
                ")b\n" +
                "ON a.颜色 = b.颜色\n" +
                "LEFT JOIN\n" +
                "(\n" +
                "\tSELECT  concat(c.外观,'/',c.内饰) 颜色,count(*) 未分配资源\n" +
                "                \t      FROM   LSH车型 a, LSH库存表 b LEFT JOIN vw_stock_quotation c ON c.库存编号 = b.库存编号    \n" +
                "                \t      WHERE a.车型编号 = b.车型编号  \n" +
                "                         AND b.批发日期 is NULL and b.is_delete = 0 and b.车架号 not like 'IRIS%' \n" +
                "                         AND  (c.开票时间 IS NULL \n" +
                "                               OR (date_format(c.开票时间,'%Y-%m-%d') > :endDate and c.退票时间 IS NULL) \n" +
                "                               OR (date_format(c.开票时间,'%Y-%m-%d') > :endDate and c.退票时间 < c.开票时间) \n" +
                "                              ) \n" +
                "               and b.所属经销商 = :permit and 1=1" +
                "GROUP BY c.外观,c.内饰 \n" +
                ")c\n" +
                "ON a.颜色 = c.颜色";
        Map map = Maps.newHashMap();
        sql = sql.replace("1=1","a.品牌 = :selBrand and a.级别 = :selClass and a.车型 = :selType ");
        map.put("selBrand",info.get("selBrand"));
        map.put("selClass",info.get("selClass"));
        map.put("selType",info.get("selType"));
        map.put("permit",info.get("permit"));
        List list = DateFormatUtils.getAfterDate();
        map.put("startDate",list.get(0));
        map.put("endDate",list.get(1));
        return this.getList(sql,map);
    }

    private List getModelLineData(Map info){
        String sql = "\n" +
                "select a.车款,a.报价数,IFNULL(b.库存,0)库存,IFNULL(未分配资源,0)未分配资源\n" +
                "from\n" +
                "(\n" +
                "select 车款, sum(报价数) 报价数 \n" +
                "from (\n" +
                "\t\t\t\tselect a.品牌, a.级别,a.车型,a.车型详细 车款, 1 AS 报价数 from \n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\tselect a.初始报价单号,a.许可,b.车型, 来源, b.品牌, b.级别,b.车型详细\n" +
                "\t\t\t\t\t\t\t\tFROM 报价单表 a, LSH车型 b \n" +
                "\t\t\t\t\t\t\t\tWHERE a.车型编号 = b.车型编号\n" +
                "\t\t\t\t\t\t\t\tAND date_format(a.`报价时间`,'%Y-%m-%d') >= :startDate and date_format(a.`报价时间`,'%Y-%m-%d') <= :endDate  \n" +
                "                and a.许可 = :permit \n" +
                "\t\t\t\t\t\t\t\tORDER BY 报价编号 DESC\n" +
                "\t\t\t\t)a GROUP BY 初始报价单号  having 1=1 \n" +
                ")a GROUP BY 车款\n" +
                ")a\n" +
                "LEFT JOIN\n" +
                "\n" +
                "(\n" +
                "SELECT  a.车型详细 车款,count(*) 库存\n" +
                "                \t      FROM   LSH车型 a, LSH库存表 b LEFT JOIN vw_stock_quotation c ON c.库存编号 = b.库存编号    \n" +
                "                \t      WHERE a.车型编号 = b.车型编号  \n" +
                "                         AND b.批发日期 <= :endDate and b.is_delete = 0 and b.车架号 not like 'IRIS%' \n" +
                "                         AND  (c.开票时间 IS NULL \n" +
                "                               OR (date_format(c.开票时间,'%Y-%m-%d') > :endDate and c.退票时间 IS NULL) \n" +
                "                               OR (date_format(c.开票时间,'%Y-%m-%d') > :endDate and c.退票时间 < c.开票时间) \n" +
                "                              ) \n" +
                "               and b.所属经销商 = :permit and 1=1\n" +
                "GROUP BY a.车型详细 \n" +
                ")b\n" +
                "ON a.车款 = b.车款\n" +
                "LEFT JOIN\n" +
                "(\n" +
                "\tSELECT  a.车型详细 车款,count(*) 未分配资源\n" +
                "                \t      FROM   LSH车型 a, LSH库存表 b LEFT JOIN vw_stock_quotation c ON c.库存编号 = b.库存编号    \n" +
                "                \t      WHERE a.车型编号 = b.车型编号  \n" +
                "                         AND b.批发日期 is NULL and b.is_delete = 0 and b.车架号 not like 'IRIS%' \n" +
                "                         AND  (c.开票时间 IS NULL \n" +
                "                               OR (date_format(c.开票时间,'%Y-%m-%d') > :endDate and c.退票时间 IS NULL) \n" +
                "                               OR (date_format(c.开票时间,'%Y-%m-%d') > :endDate and c.退票时间 < c.开票时间) \n" +
                "                              ) \n" +
                "                 and b.所属经销商 = :permit and 1=1\n" +
                "GROUP BY a.车型详细\n" +
                ")c\n" +
                "ON a.车款 = c.车款\n";
        Map map = Maps.newHashMap();
        sql = sql.replace("1=1","a.品牌 = :selBrand and a.级别 = :selClass and a.车型 = :selType ");
        map.put("selBrand",info.get("selBrand"));
        map.put("selClass",info.get("selClass"));
        map.put("selType",info.get("selType"));
        map.put("permit",info.get("permit"));
        List list = DateFormatUtils.getAfterDate();
        map.put("startDate",list.get(0));
        map.put("endDate",list.get(1));
        return this.getList(sql,map);
    }

    private List getModelAndColorLineData(Map info){
        String sql = "\n" +
                "select a.车款,a.颜色,a.报价数-IFNULL(b.库存,0)-IFNULL(未分配资源,0) 新增库存,concat(IFNULL(a.颜色,''),IFNULL(a.车款,''),'_采购预测') 预计新增采购数\n" +
                "from\n" +
                "(\n" +
                "select 车型详细 车款,concat(a.外观,'/',a.内饰) 颜色, sum(报价数) 报价数 \n" +
                "from (\n" +
                "\t\t\t\tselect a.品牌, a.级别,a.车型,a.车型详细,a.外观,a.内饰, 1 AS 报价数 from \n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\tselect a.初始报价单号,a.许可,b.车型, 来源, b.品牌, b.级别,a.外观,a.内饰,b.车型详细\n" +
                "\t\t\t\t\t\t\t\tFROM 报价单表 a, LSH车型 b \n" +
                "\t\t\t\t\t\t\t\tWHERE a.车型编号 = b.车型编号\n" +
                "\t\t\t\t\t\t\t\tAND date_format(a.`报价时间`,'%Y-%m-%d') >= :startDate and date_format(a.`报价时间`,'%Y-%m-%d') <= :endDate  \n" +
                "                and a.许可 = :permit \n" +
                "\t\t\t\t\t\t\t\tORDER BY 报价编号 DESC\n" +
                "\t\t\t\t)a GROUP BY 初始报价单号 having 1=1\n" +
                ")a GROUP BY 车型详细,外观,内饰\n" +
                ")a\n" +
                "LEFT JOIN\n" +
                "\n" +
                "(\n" +
                "SELECT  a.车型详细 车款,concat(c.外观,'/',c.内饰) 颜色,count(*) 库存\n" +
                "                \t      FROM   LSH车型 a, LSH库存表 b LEFT JOIN vw_stock_quotation c ON c.库存编号 = b.库存编号    \n" +
                "                \t      WHERE a.车型编号 = b.车型编号  \n" +
                "                         AND b.批发日期 <= :endDate and b.is_delete = 0 and b.车架号 not like 'IRIS%' \n" +
                "                         AND  (c.开票时间 IS NULL \n" +
                "                               OR (date_format(c.开票时间,'%Y-%m-%d') > :endDate and c.退票时间 IS NULL) \n" +
                "                               OR (date_format(c.开票时间,'%Y-%m-%d') > :endDate and c.退票时间 < c.开票时间) \n" +
                "                              ) \n" +
                "                 and b.所属经销商 = :permit and 1=1\n" +
                "GROUP BY 车型详细,c.外观,c.内饰 \n" +
                ")b\n" +
                "ON a.车款 = b.车款 and a.颜色 = b.颜色\n" +
                "LEFT JOIN\n" +
                "(\n" +
                "\tSELECT  a.车型详细 车款,concat(c.外观,'/',c.内饰) 颜色,count(*) 未分配资源\n" +
                "                \t      FROM   LSH车型 a, LSH库存表 b LEFT JOIN vw_stock_quotation c ON c.库存编号 = b.库存编号    \n" +
                "                \t      WHERE a.车型编号 = b.车型编号  \n" +
                "                         AND b.批发日期 is NULL and b.is_delete = 0 and b.车架号 not like 'IRIS%' \n" +
                "                         AND  (c.开票时间 IS NULL \n" +
                "                               OR (date_format(c.开票时间,'%Y-%m-%d') > :endDate and c.退票时间 IS NULL) \n" +
                "                               OR (date_format(c.开票时间,'%Y-%m-%d') > :endDate and c.退票时间 < c.开票时间) \n" +
                "                              ) \n" +
                "                 and b.所属经销商 = :permit and 1=1\n" +
                "GROUP BY 车型详细,c.外观,c.内饰 \n" +
                ")c\n" +
                "ON a.车款 = c.车款 and a.颜色 = c.颜色";
        Map map = Maps.newHashMap();
        sql = sql.replace("1=1","a.品牌 = :selBrand and a.级别 = :selClass and a.车型 = :selType ");
        map.put("selBrand",info.get("selBrand"));
        map.put("selClass",info.get("selClass"));
        map.put("selType",info.get("selType"));
        map.put("permit",info.get("permit"));
        List list = DateFormatUtils.getAfterDate();
        map.put("startDate",list.get(0));
        map.put("endDate",list.get(1));
        return this.getList(sql,map);
    }

    private List getModelAndColorLineDateAll(Map info) {
        String sql = "select a.品牌,a.级别,a.车型,a.车款,a.颜色,a.报价数-IFNULL(b.库存,0)-IFNULL(未分配资源,0) 新增库存,concat(IFNULL(a.颜色,''),IFNULL(a.车款,''),'_采购预测') 预计新增采购数\n" +
                " \n" +
                "                 from\n" +
                "                 (\n" +
                "                 select a.品牌,a.级别,a.车型,车型详细 车款,concat(a.外观,'/',a.内饰) 颜色, sum(报价数) 报价数 \n" +
                "                 from (\n" +
                "                     select a.品牌, a.级别,a.车型,a.车型详细,a.外观,a.内饰, 1 AS 报价数 from \n" +
                "                         (\n" +
                "                         select a.初始报价单号,a.许可,b.车型, 来源, b.品牌, b.级别,a.外观,a.内饰,b.车型详细\n" +
                "                         FROM 报价单表 a, LSH车型 b \n" +
                "                         WHERE a.车型编号 = b.车型编号\n" +
                "                         AND date_format(a.`报价时间`,'%Y-%m-%d') >= :startDate and date_format(a.`报价时间`,'%Y-%m-%d') <= :endDate  \n" +
                "                                 and a.许可 = :permit \n" +
                "                         ORDER BY 报价编号 DESC\n" +
                "                     )a GROUP BY 初始报价单号 having 1=1\n" +
                "                 )a GROUP BY 车型详细,外观,内饰\n" +
                "                 )a\n" +
                "                 LEFT JOIN\n" +
                "                 \n" +
                "                 (\n" +
                "                 SELECT  a.品牌,a.级别,a.车型,a.车型详细 车款,concat(c.外观,'/',c.内饰) 颜色,count(*) 库存\n" +
                "                                        FROM   LSH车型 a, LSH库存表 b LEFT JOIN vw_stock_quotation c ON c.库存编号 = b.库存编号    \n" +
                "                                        WHERE a.车型编号 = b.车型编号  \n" +
                "                                          AND b.批发日期 <= :endDate and b.is_delete = 0 and b.车架号 not like 'IRIS%' \n" +
                "                                          AND  (c.开票时间 IS NULL \n" +
                "                                                OR (date_format(c.开票时间,'%Y-%m-%d') > :endDate and c.退票时间 IS NULL) \n" +
                "                                                OR (date_format(c.开票时间,'%Y-%m-%d') > :endDate and c.退票时间 < c.开票时间) \n" +
                "                                               ) \n" +
                "                                  and b.所属经销商 = :permit and 1=1\n" +
                "                 GROUP BY 车型详细,c.外观,c.内饰 \n" +
                "                 )b\n" +
                "                 ON a.车款 = b.车款 and a.颜色 = b.颜色\n" +
                "                 LEFT JOIN\n" +
                "                 (\n" +
                "                  SELECT  a.品牌,a.级别,a.车型,a.车型详细 车款,concat(c.外观,'/',c.内饰) 颜色,count(*) 未分配资源\n" +
                "                                        FROM   LSH车型 a, LSH库存表 b LEFT JOIN vw_stock_quotation c ON c.库存编号 = b.库存编号    \n" +
                "                                        WHERE a.车型编号 = b.车型编号  \n" +
                "                                          AND b.批发日期 is NULL and b.is_delete = 0 and b.车架号 not like 'IRIS%' \n" +
                "                                          AND  (c.开票时间 IS NULL \n" +
                "                                                OR (date_format(c.开票时间,'%Y-%m-%d') > :endDate and c.退票时间 IS NULL) \n" +
                "                                                OR (date_format(c.开票时间,'%Y-%m-%d') > :endDate and c.退票时间 < c.开票时间) \n" +
                "                                               ) \n" +
                "                                  and b.所属经销商 = :permit and 1=1\n" +
                "                 GROUP BY 车型详细,c.外观,c.内饰 \n" +
                "                 )c\n" +
                "                 ON a.车款 = c.车款 and a.颜色 = c.颜色";
        Map map = Maps.newHashMap();
        List list = DateFormatUtils.getAfterDate();
        map.put("startDate",list.get(0));
        map.put("endDate",list.get(1));
        map.put("permit",info.get("permit"));
        return this.getList(sql,map);
    }
}
